package cn.fldong.dao.impl;

import cn.fldong.dao.UserDAO;
import cn.fldong.model.User;
import cn.fldong.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * UserDAO接口的实现类，提供用户相关的数据库操作实现
 */
public class UserDAOImpl implements UserDAO {
    private static final String GET_USER_BY_ID = "SELECT * FROM users WHERE user_id = ?";
    private static final String GET_USER_BY_USERNAME = "SELECT * FROM users WHERE username = ?";
    private static final String GET_ALL_USERS = "SELECT * FROM users ORDER BY user_id";
    private static final String ADD_USER = "INSERT INTO users(username, password, full_name, email, phone, role, status) VALUES(?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_USER = "UPDATE users SET username=?, password=?, full_name=?, email=?, phone=?, role=?, status=? WHERE user_id=?";
    private static final String DELETE_USER = "DELETE FROM users WHERE user_id = ?";
    private static final String LOGIN = "SELECT * FROM users WHERE username = ? AND password = ?";

    /**
     * 将ResultSet映射为User对象的私有辅助方法
     */
    private User mapResultSetToUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setUserId(rs.getInt("user_id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setFullName(rs.getString("full_name"));
        user.setEmail(rs.getString("email"));
        user.setPhone(rs.getString("phone"));
        user.setRole(rs.getString("role"));
        user.setStatus(rs.getString("status"));
        user.setCreatedAt(rs.getTimestamp("created_at"));
        user.setUpdatedAt(rs.getTimestamp("updated_at"));
        return user;
    }

    @Override
    public User findUserById(String userID) throws SQLException {
        int userId = Integer.parseInt(userID);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        User user = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_USER_BY_ID);
            stmt.setInt(1, userId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                user = mapResultSetToUser(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return user;
    }
    
    @Override
    public boolean usernameExists(String username) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM users WHERE username = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }
    }

    @Override
    public User findUserByUsername(String username) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        User user = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_USER_BY_USERNAME);
            stmt.setString(1, username);
            rs = stmt.executeQuery();

            if (rs.next()) {
                user = mapResultSetToUser(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return user;
    }

    @Override
    public List<User> findAllUsers() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_ALL_USERS);
            rs = stmt.executeQuery();

            while (rs.next()) {
                userList.add(mapResultSetToUser(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return userList;
    }

    @Override
    public boolean addUser(User user) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(ADD_USER);
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getFullName());
            stmt.setString(4, user.getEmail());
            stmt.setString(5, user.getPhone());
            stmt.setString(6, user.getRole());
            stmt.setString(7, user.getStatus());
            if (stmt.executeUpdate() > 0){
                return true;
            }else {
                return false;
            }
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean updateUser(User user) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(UPDATE_USER);
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getFullName());
            stmt.setString(4, user.getEmail());
            stmt.setString(5, user.getPhone());
            stmt.setString(6, user.getRole());
            stmt.setString(7, user.getStatus());
            stmt.setInt(8, user.getUserId());
            if (stmt.executeUpdate() > 0) {
                return true;
            }else {
                return false;
            }
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean deleteUser(int userId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(DELETE_USER);
            stmt.setInt(1, userId);
            if (stmt.executeUpdate() > 0) {
                return true;
            }else {
                return false;
            }
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public User findUserByUsernameAndPassword(String username, String password) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        User user = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(LOGIN);
            stmt.setString(1, username);
            stmt.setString(2, password);
            rs = stmt.executeQuery();

            if (rs.next()) {
                user = mapResultSetToUser(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return user;
    }
}